********************************************************************************
*                                                                              *
*                         CREATE FIRM PRODUCTION STATISTICS                    *
*                                                                              *
********************************************************************************


global minyear = 2009
global maxyear = 2020
cd "G:\Bedrijven\"
global wave20092020 = 1
global wave20002008 = 1
global wave20002018 = 0
global healthcare = 0

********************************************************************************
***************************     2009-2020     **********************************
********************************************************************************

if $wave20092020 == 1 {
foreach sector in autohandel bouwnijverheid commercielediensten delfstoffenwinning detailhandel dienstverleninglandbouw energie groothandel industrie onroerendgoed transport waterafval { 
local sector2 `sector'

forvalues year = $minyear(1)$maxyear {
		if `year' == 2009 {
			import spss using "G:\Bedrijven\PS_`sector'\\`year'\130911 PS_`sector'_main `year'V4.sav", case(lower) clear
		}
		if `year' == 2010 & "`sector'" != "onroerendgoed" {
			import spss using "G:\Bedrijven\PS_`sector'\2010\130130 PS_`sector2'_main 2010V3.sav", case(lower) clear
		}
		if `year' == 2010 & "`sector'" == "onroerendgoed" {
			import spss using "G:\Bedrijven\PS_`sector'\2010\120827 PS_Onroerendgoed_main 2010V1.sav", case(lower) clear
		}
		if `year' == 2011 {
			import spss using "G:\Bedrijven\PS_`sector'\2011\130916 PS_`sector2'_main 2011V1.sav", case(lower) clear
		}
		if `year' == 2012 {
			import spss using "G:\Bedrijven\PS_`sector'\2012\140714 PS_`sector2'_main 2012V1.sav", case(lower) clear
		}
		if `year' == 2013 {
			import spss using "G:\Bedrijven\PS_`sector'\\`year'\PS_`sector2'_main `year'V1.sav", case(lower) clear
		}
		if `year' == 2014 | `year' == 2016 | `year' == 2017 | `year' == 2018 | `year' == 2019 | `year' == 2020 {
			import spss using "G:\Bedrijven\PS_`sector'\\`year'\PS_`sector2'_main_`year'V1.sav", case(lower) clear
		}
		
		if `year' == 2015 {
			import spss using "G:\Bedrijven\PS_`sector'\\`year'\PS_`sector2'_main_`year'V2.sav", case(lower) clear
		}

		g sector = "`sector'"
		g year = `year'
		g empl = persons100000	
		g valueadded = out_brutotw200000
		g sales = omzetph210000
		g totalcosts = bedrlst310000
		g personnelcosts = perslst100000
		g wagebill = loonsom110002
		g capitalcosts = afschrg110000
		g automationcosts = bedrlst348400
		g machinecosts = bedrlst344100+ bedrlst344900
		//g totalwagebill = loonsom110002+loonsom121100+loonsom122000+loonsom122009
	
		keep be_id sbi sector year empl valueadded sales totalcosts personnelcosts *wagebill *costs
		rename be_id beid

		do "H:\Robots and Workers\Do-files\Variable labels.do"
		save "H:\Robots and Workers\Data\PS_`sector'_`year'.dta", replace
	}

	clear all
	forvalues year = $minyear(1)$maxyear {
		append using "H:\Robots and Workers\Data\PS_`sector'_`year'.dta"
		erase "H:\Robots and Workers\Data\PS_`sector'_`year'.dta"
	}
	save "H:\Robots and Workers\Data\PS_`sector'.dta", replace

}


clear all
foreach sector in autohandel bouwnijverheid commercielediensten delfstoffenwinning detailhandel dienstverleninglandbouw energie groothandel industrie onroerendgoed transport waterafval { 
	append using "H:\Robots and Workers\Data\PS_`sector'.dta"
	erase "H:\Robots and Workers\Data\PS_`sector'.dta"
}
save "H:\Robots and Workers\Data\ProductionStatistics.dta", replace

// 	foreach sector in autohandel bouwnijverheid commercielediensten delfstoffenwinning detailhandel dienstverleninglandbouw energie groothandel industrie onroerendgoed transport waterafval { 
// 		disp "`sector'"
// 		tab year if sector == "`sector'"
// 	}
}


********************************************************************************
***************************     2000-2008     **********************************
********************************************************************************

if $wave20002008 == 1 {
    
foreach sector in bouwnijverheid commercielediensten delfstoffenwinning detailhandel dienstverleninglandbouw energiewater groothandel industrie milieudienstverlening speurontwikkeling transport { 

forvalues year = 2000(1)2008 {
		if "`sector'" == "bouwnijverheid" {
			local sector2 F_Bouwnijverheid
		}
		if "`sector'" == "commercielediensten" {
		    if `year'<= 2005 {
				local sector2 H_50-55-71-72-74-93_commercielediensten
			}
			if `year'==2006 { 
				local sector2 H_50_55_71_72_74_93_commercielediensten
			}
			if `year'==2007 | `year'==2008 {
			    local sector2 H_commercielediensten
			}
		}		
		if "`sector'" == "delfstoffenwinning" {
			local sector2 C_delfstoffenwinning
		}	
		if "`sector'" == "detailhandel" {
		    if `year'<= 2007 {
				local sector2 G_52_detailhandel
			}
			if `year'==2008 {
			    local sector2 G_detailhandel
			}
		}			
		if "`sector'" == "energiewater" {
			local sector2 E_energiewater
		}	
		if "`sector'" == "groothandel" {
		    if `year'<= 2006 {
				local sector2 G_51_groothandel
			}
			if `year'==2007 {
			    local sector2 G_groothandel
			}
		}	
		
		if "`sector'" == "industrie" {
			local sector2 D_industrie
		}	
		if "`sector'" == "transport" {
			local sector2 I_transport
		}			
		if "`sector'" == "dienstverleninglandbouw" {
		    if `year'<= 2007 {
				local sector2 A_014_dienstverleninglandbouw
			}
			if `year'==2008 {
			    local sector2 A_dienstverleninglandbouw
			}
		}	
		if "`sector'" == "milieudienstverlening" {
			if `year'<= 2007 {
				local sector2 O_90_milieudienstverlening
			}
			if `year'==2008 {
			  local sector2 O_milieudienstverlening 
			}
		}
		if "`sector'" == "speurontwikkeling" {
			local sector2 K_speurontwikkeling
		}	
		//import spss using "G:\Bedrijven\PS_COMMERCIELEDIENSTEN\2000\081126 PS_H_50-55-71-72-74-93_CommercieleDiensten_main 2000V1.sav", case(lower) clear
		
		clear all
		if `year' == 2000 {
		    capture import spss using "G:\Bedrijven\PS_`sector'\2001\081113 PS_`sector2'_main 2001V1.sav", case(lower) clear // Dienstverlening landbouw wave 2000 is missing - we drop 2000 later
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081118 PS_`sector2'_main 2001V1.sav", case(lower) clear // Energiewater wave 2000 is missing - we drop 2000 later
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081118 PS_`sector2'_main 2002V1.sav", case(lower) clear // Milieudienstverlening wave 2000 is missing - we drop 2000 later
			capture import spss using "G:\Bedrijven\PS_`sector'\2000\081119 PS_`sector2'_main 2000V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2000\081126 PS_`sector2'_main 2000V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2000\081114 PS_`sector2'_main 2000V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2000\081125 PS_`sector2'_main 2000V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2000\081121 PS_`sector2'_main 2000V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2000\081111 PS_`sector2'_main 2000V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2000\081120 PS_`sector2'_main 2000V1.sav", case(lower) clear
		}
		if `year' == 2001 {
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081118 PS_`sector2'_main 2002V1.sav", case(lower) clear // Milieudienstverlening wave 2001 is missing - we drop 2001 later
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081119 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081126 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081114 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081125 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081113 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081118 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081124 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081112 PS_`sector2'_main 2001V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2001\081120 PS_`sector2'_main 2001V1.sav", case(lower) clear
		}
		if `year' == 2002 {
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081119 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081121 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081117 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081125 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081113 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081118 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081124 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081112 PS_`sector2'_main 2002V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2002\081120 PS_`sector2'_main 2002V1.sav", case(lower) clear
		}
		if `year' == 2003 {
		    capture import spss using "G:\Bedrijven\PS_`sector'\2003\081120 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081126 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081117 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081125 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081114 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081118 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081124 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081112 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081119 PS_`sector2'_main 2003V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2003\081120 PS_`sector2'_main 2003V1.sav", case(lower) clear
		}	
		if `year' == 2004 {
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081120 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081126 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081117 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081125 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081114 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081118 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081124 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081112 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081119 PS_`sector2'_main 2004V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2004\081121 PS_`sector2'_main 2004V1.sav", case(lower) clear
		}
		if `year' == 2005 {
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081121 PS_`sector2'_main 2005V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081126 PS_`sector2'_main 2005V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081117 PS_`sector2'_main 2005V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081125 PS_`sector2'_main 2005V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081114 PS_`sector2'_main 2005V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081119 PS_`sector2'_main 2005V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081124 PS_`sector2'_main 2005V1.sav", case(lower) clear	
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081113 PS_`sector2'_main 2005V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2005\081120 PS_`sector2'_main 2005V1.sav", case(lower) clear
		}
		if `year' == 2006 {
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090916 PS_`sector2'_main 2006V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090925 PS_`sector2'_main 2006V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090921 PS_`sector2'_main 2006V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\081210 PS_`sector2'_main 2006V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090914 PS_`sector2'_main 2006V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090914 PS_`sector2'_main 2006V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090909 PS_`sector2'_main 2006V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090929 PS_`sector2'_main 2006V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\090928 PS_`sector2'_main 2006V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2006\081211 PS_`sector2'_main 2006V1.sav", case(lower) clear
		}
		if `year' == 2007 {
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\090916 PS_`sector2'_main 2007V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\090916 PS_`sector2'_main 2007V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\101018 PS_`sector2'_main 2007V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\090923 PS_`sector2'_main 2007V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\090924 PS_`sector2'_main 2007V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\090831 PS_`sector2'_main 2007V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\090929 PS_`sector2'_main 2007V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\101012 PS_`sector2'_main 2007V2.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2007\090929 PS_`sector2'_main 2007V1.sav", case(lower) clear
		}
		if `year' == 2008 {
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100811 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100901 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100826 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100922 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100916 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100827 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100830 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100804 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100921 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100908 PS_`sector2'_main 2008V1.sav", case(lower) clear
			capture import spss using "G:\Bedrijven\PS_`sector'\2008\100915 PS_`sector2'_main 2008V1.sav", case(lower) clear
		}

		g sector = "`sector'"
		g year = `year'
		g empl = persons100000	
		if `year'<= 2005 {
			g valueadded = brutotw000000
			g wagebill = loonsom110000
			g sales = marge1000000
			g personnelcosts = bedrlst345999
			
		}
		if `year'==2006 | `year'==2007 | `year'==2008 {
			foreach var in subsidi140000 subsidi130000 {
			    replace `var' = 0 if `var' == .
			}
			g valueadded = loonsom110002+afschrg110000+results120000+(subsidi140000+subsidi130000)
			g wagebill = loonsom110002
			g sales = omzetps210000
			g personnelcosts = bedrlst345900+loonsom110002
			
			
			
			
		}
				
		g totalcosts = bedrlst310000
		g capitalcosts = afschrg110000
		g automationcosts = bedrlst348400
		g machinecosts = bedrlst344100+ bedrlst344900
	
		capture rename be_id beid
		keep beid sbi93_5d sector year empl valueadded sales totalcosts personnelcosts *wagebill  *costs
		

		do "H:\Robots and Workers\Do-files\Variable labels.do"
		save "H:\Robots and Workers\Data\PS_`sector'_`year'.dta", replace
	}

	clear all
	forvalues year = 2000(1)2008 {
		append using "H:\Robots and Workers\Data\PS_`sector'_`year'.dta"
		erase "H:\Robots and Workers\Data\PS_`sector'_`year'.dta"
	}

	if "`sector'" == "dienstverleninglandbouw" | "`sector'" == "energiewater"  { 
	    drop if year == 2000
	}
	if "`sector'" == "milieudienstverlening" { 
	    drop if year == 2000 | year == 2001
	}
	save "H:\Robots and Workers\Data\PS_`sector'.dta", replace
	
}


clear all
foreach sector in bouwnijverheid commercielediensten delfstoffenwinning detailhandel dienstverleninglandbouw energiewater groothandel industrie milieudienstverlening speurontwikkeling transport { 
	append using "H:\Robots and Workers\Data\PS_`sector'.dta"
	erase "H:\Robots and Workers\Data\PS_`sector'.dta"
}

duplicates drop beid year, force
destring beid, force replace
save "H:\Robots and Workers\Data\ProductionStatistics_20002008.dta", replace
append using "H:\Robots and Workers\Data\ProductionStatistics.dta"
sort beid year
duplicates drop beid year, force
save "H:\Robots and Workers\Data\ProductionStatistics.dta", replace
erase "H:\Robots and Workers\Data\ProductionStatistics_20002008.dta"

}



if $healthcare == 1 {
	local sector healthcare

	forvalues year = 2011(1)2019 {
		import spss using "G:\GezondheidWelzijn\PS_ZORGINSTELLINGEN\PS_Zorginstellingen`year'V1.sav", case(lower) clear
		g sector = "`sector'"
		g year = `year'
		g valueadded = a16+a19+a32+a12
		g wagebill = a16
		g sales = a2
		g personnelcosts = a16+a22+a24
		g totalcosts = a14
		g capitalcosts = a19
		g empl = e1
		capture rename be_id beid
		capture rename abrgroepnr beid
		rename sbi sbi
		keep beid sbi sector year empl valueadded sales totalcosts personnelcosts *wagebill capitalcosts
		
		save "H:\Robots and Workers\Data\PS_`sector'_`year'.dta", replace
		
	}
		
	import spss using "G:\GezondheidWelzijn\PS_ZORGPRAKTIJKEN\2005\140703 PS_Zorgpraktijken 2005V2.sav", case(lower) clear
	import spss using "G:\GezondheidWelzijn\PS_ZORGPRAKTIJKENREVISIE\PS Zorgpraktijken 2015 voorlopig.sav", case(lower) clear
	import spss using "G:\GezondheidWelzijn\PS_ZORGPRAKTIJKENREVISIE\PS Zorgpraktijken 2016 voorlopig.sav", case(lower) clear
	import spss using "G:\GezondheidWelzijn\PS_ZORGPRAKTIJKENREVISIE\PS Zorgpraktijken 2017 voorlopig.sav", case(lower) clear
		g sector = "`sector'"
		g year = `year'
		g valueadded = r10+r12+r16+r6
		g wagebill = r10
		g sales = r2
		g personnelcosts = r9
		g totalcosts = r8
		g capitalcosts = r12
		g empl = .
	
}


